﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using System.Diagnostics;

using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Xml;
using System.Net;
using Microsoft.Win32;
using System.Security.Permissions;
using ASTraceAnalyzer;

namespace SSASQryPrfHlpr
{
    public partial class frmMain : Form
    {
        private ASTraceAnalyzer.ASTraceAnalyzer asta = new ASTraceAnalyzer.ASTraceAnalyzer();
        private delegate void UpdateGridDelegate();
        bool IsKudosSiteReachable = false;
        bool bAbortFlag = false;
        
        
        public string[] args;
        bool TextChangedFromUserInput = true;
        System.Windows.Forms.Timer StatusTimer = new System.Windows.Forms.Timer();

        public frmMain()
        {
            InitializeComponent();
            StatusTimer.Interval = 200;
            StatusTimer.Tick += StatusTimer_Tick;

            toolTip1.SetToolTip(txtQuery, "After making modifications to the de-parameterized query and pasting them in the textbox on the far right\r\n" +
                                          "this parameterized query text will be updated with parameters specified in XML in the middle textbox.\r\n" +
                                          "This modified query can then be pasted back into the original source from which the query was captured.\r\n" +
                                          "Parameterized query text may also be pasted here directly to de-parameterize without loading queries from file.");
            toolTip1.SetToolTip(txtDPQuery, "When a query is selected from a trace file or pasted into the textbox to the far left the query is de-parameterized\r\n" +
                                            "with parameters specified in XML in the middle textbox.  Use the de-parameterized version of the query here to\r\n" +
                                            "experiment with its performance.  Then paste an improved query back into this textbox to re-parameterize.\r\n" +
                                            "Use the re-parameterized query directly in the original source!  Voilà!");
            toolTip1.SetToolTip(txtParams, "Queries are parameterized and de-parameterized based on the parameters XML, displayed here.\r\nEdits to this XML will update both the parameterized and de-parameterized versions of the query.");

            Task<IPHostEntry> t = System.Net.Dns.GetHostEntryAsync("kudos");
            t.ContinueWith(delegate { IsKudosSiteReachable = t.Exception == null; });
        }

        void StatusTimer_Tick(object sender, EventArgs e)
        {
            StatusText2.Text = "Parsed " + asta.iRecordCount.ToString("#,0") + " records.";
            StatusText.Text = "Currently parsing file:  " + asta.trc;
        }       

        void UpdateGrid()
        {
            foreach (ASTraceAnalyzer.Query q in (chkIncludeNoDurationQueries.Checked ? asta.LongestXQueries.OrderByDescending(q => q.Duration).Concat(asta.QueriesWithoutEndTiming) : asta.LongestXQueries.OrderByDescending(q => q.Duration)))
            {
                dgQueries.Rows.Add(new object[] { q.StartRow, q.File, q.Duration, "", q.Text, q.Parameters });
                if (q.End == null)
                {
                    dgQueries.Rows[dgQueries.Rows.Count - 1].Cells[2].Style.BackColor = SystemColors.GrayText;
                    dgQueries.Rows[dgQueries.Rows.Count - 1].Cells[2].Style.ForeColor = SystemColors.ControlLightLight;
                    dgQueries.Rows[dgQueries.Rows.Count - 1].Cells[2].ToolTipText += "This incompleted query has no QueryEnd event.  Duration reflects milliseconds from start of query to end of this trace.";
                }

                float FEPct = (float)q.FEDuration / (float)q.Duration;
                float SEPct = (float)q.SEDuration / (float)q.Duration;
                int red = 220, green = 220, blue = 220;
                if (FEPct > SEPct)
                {
                    dgQueries.Rows[dgQueries.Rows.Count - 1].Cells[3].ToolTipText = "FE dominates by " + (((FEPct - SEPct) / FEPct) * 100).ToString("0") + "%";
                    green = blue = (int)((1 - ((FEPct - SEPct)/FEPct)) * 220);
                }
                else
                {
                    dgQueries.Rows[dgQueries.Rows.Count - 1].Cells[3].ToolTipText = "SE dominates by " + (((SEPct - FEPct) / SEPct) * 100).ToString("0") + "%";
                    green = red = (int)((1 - ((SEPct - FEPct)/SEPct)) * 220);
                }

                dgQueries.Rows[dgQueries.Rows.Count - 1].Cells[3].Style.BackColor = Color.FromArgb(red, green, blue);
                dgQueries.Rows[dgQueries.Rows.Count - 1].Cells[3].Style.SelectionBackColor = Color.FromArgb(red, green, blue);

            }
            dgQueries.Columns["QueryColumn"].AutoSizeMode = DataGridViewAutoSizeColumnMode.NotSet;
            dgQueries.Columns["RowNumber"].AutoSizeMode = DataGridViewAutoSizeColumnMode.NotSet;
            pnlParseOptions.Enabled = true;
            dgQueries.Enabled = true;
            dgQueries_CurrentCellChanged(null, new EventArgs());
            StatusTimer.Stop();
            btnCancel.Visible = false;
            if (bAbortFlag) StatusText2.Text += "  Parsing cancelled."; else if (!StatusText2.Text.Contains("Complete")) StatusText2.Text += "  Complete.";
            StatusText.Text = "Loaded trace at " + asta.trc;
            dgQueries.Columns["QueryColumn"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            dgQueries.Columns["QueryColumn"].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
        }      

        private void btnLoadTrace_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                asta.ProcessTrace(openFileDialog1.FileName, chkRollover.Checked, Convert.ToInt32(udLongestCount.Value), chkDistinct.Checked, 
                    new ASTraceAnalyzer.ASTraceAnalyzer.ASTraceSetTextDelegate(SetTextCallback), new ASTraceAnalyzer.ASTraceAnalyzer.ASTraceUpdateDelegate(UpdateCallback));
        }

        private void ProcessTraceWithUI()
        {
            dgQueries.Rows.Clear();
            pnlParseOptions.Enabled = false;
            dgQueries.Enabled = false;
            btnCancel.Enabled = true;
            btnCancel.Visible = true;
            txtDPQuery.Text = txtParams.Text = txtQuery.Text = "";
            txtDPQuery.BackColor = txtQuery.BackColor = txtParams.BackColor = Color.Empty;

            StatusTimer.Start();

            asta.ProcessTrace(openFileDialog1.FileName, chkRollover.Checked, Convert.ToInt32(udLongestCount.Value), chkDistinct.Checked,
                new ASTraceAnalyzer.ASTraceAnalyzer.ASTraceSetTextDelegate(SetTextCallback), new ASTraceAnalyzer.ASTraceAnalyzer.ASTraceUpdateDelegate(UpdateCallback));

            if (asta.bMultipleFiles)
                dgQueries.Columns["TraceFile"].Visible = true;
            else 
                dgQueries.Columns["TraceFile"].Visible = false;
        }

        private void dgQueries_CurrentCellChanged(object sender, EventArgs e)
        {
            if (dgQueries.CurrentCell != null)
            {
                txtQuery.Text = Convert.ToString(dgQueries.Rows[dgQueries.CurrentCell.RowIndex].Cells[dgQueries.Columns["QueryColumn"].Index].Value);
                txtParams.Text = (string)dgQueries.Rows[dgQueries.CurrentCell.RowIndex].Cells[dgQueries.Columns["Parameters"].Index].Value;
                DeParameterize();
            }
            txtDPQuery.BackColor = Color.Empty;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            bAbortFlag = true;
            btnCancel.Enabled = false;
        }

        bool ParamsFound()
        {
            if (txtParams.Text == "Query Begin event not found to supply parameters for this query.")
            {
                txtParams.BackColor = Color.LightPink;
                txtDPQuery.BackColor = Color.Empty;
                txtQuery.BackColor = Color.Empty;
                txtDPQuery.Text = "";
                return false;
            }
            return true;
        }

        private void Parameterize()
        {
            if (ParamsFound())
            {
                string qry = txtDPQuery.Text;
                if (txtParams.Text.Trim() != "")
                    try
                    {
                        XmlDocument d = new XmlDocument();
                        d.LoadXml(txtParams.Text);
                        XmlNamespaceManager nsmgr = new XmlNamespaceManager(d.NameTable);
                        nsmgr.AddNamespace("a", d.FirstChild.Attributes[2].Value);
                        foreach (XmlNode n in d.GetElementsByTagName("Parameter"))
                        {
                            XmlNode val = n.SelectSingleNode("a:Value", nsmgr);
                            if (val.Attributes["xsi:type"].Value == "xsd:string")
                                qry = qry.Replace("\"" + n.SelectSingleNode("a:Value", nsmgr).InnerText + "\"", "@" + n.SelectSingleNode("a:Name", nsmgr).InnerText);
                            if (val.Attributes["xsi:type"].Value == "xsd:dateTime")
                                qry = qry.Replace("\"" + n.SelectSingleNode("a:Value", nsmgr).InnerText.Replace("T", " ") + "\"", "@" + n.SelectSingleNode("a:Name", nsmgr).InnerText);
                        }
                    }
                    catch (Exception ex) { }
                txtQuery.Modified = false;
                txtQuery.Text = qry;
            }
        }

        private void DeParameterize()
        {
            if (ParamsFound())
            {
                string DPQuery = txtQuery.Text;
                if (txtParams.Text.Trim() != "")
                    try
                    {
                        XmlDocument d = new XmlDocument();
                        d.LoadXml(txtParams.Text);
                        XmlNamespaceManager nsmgr = new XmlNamespaceManager(d.NameTable);
                        nsmgr.AddNamespace("a", d.FirstChild.Attributes[2].Value);
                        foreach (XmlNode n in d.GetElementsByTagName("Parameter"))
                        {
                            XmlNode val = n.SelectSingleNode("a:Value", nsmgr);
                            if (val.Attributes["xsi:type"].Value == "xsd:string")
                                DPQuery = DPQuery.Replace("@" + n.SelectSingleNode("a:Name", nsmgr).InnerText, "\"" + n.SelectSingleNode("a:Value", nsmgr).InnerText + "\"");
                            if (val.Attributes["xsi:type"].Value == "xsd:dateTime")
                                DPQuery = DPQuery.Replace("@" + n.SelectSingleNode("a:Name", nsmgr).InnerText, "\"" + n.SelectSingleNode("a:Value", nsmgr).InnerText.Replace("T", " ") + "\"");
                        }
                            
                    }
                    catch (Exception ex) { }
                txtDPQuery.Modified = false;
                txtDPQuery.Text = DPQuery;
            }
        }

        private void txtQuery_TextChanged(object sender, EventArgs e)
        {
            txtQuery.BackColor = Color.Empty;
            DeParameterize();
            if (TextChangedFromUserInput)
            {
                TextChangedFromUserInput = false;
                txtDPQuery.SelectionBackColor = Color.LightGreen;
                if (txtQuery.Text.Contains(@"<Parameters xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns=""urn:schemas-microsoft-com:xml-analysis"">") || txtQuery.Text.Contains("<PropertyList "))
                {
                    if (txtQuery.Text.Contains(@"<Parameters xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns=""urn:schemas-microsoft-com:xml-analysis"">"))
                    {
                        txtParams.Text = txtQuery.Text.Substring(txtQuery.Text.IndexOf(@"<Parameters xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns=""urn:schemas-microsoft-com:xml-analysis"">"));
                        txtParams.Text = txtParams.Text.Substring(0, txtParams.Text.IndexOf("</Parameters>") + "</Parameters>".Length);
                        txtQuery.Text = txtQuery.Text.Substring(0, txtQuery.Text.IndexOf(@"<Parameters xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns=""urn:schemas-microsoft-com:xml-analysis"">"));
                    }
                    if (txtQuery.Text.Contains("<PropertyList ")) txtQuery.Text = txtQuery.Text.Substring(0, txtQuery.Text.IndexOf("<PropertyList "));
                }
            }
            else TextChangedFromUserInput = true;
         }

        private void txtParams_TextChanged(object sender, EventArgs e)
        {
            DeParameterize();
            Parameterize();
            if (TextChangedFromUserInput)
            {
                TextChangedFromUserInput = false;
                txtDPQuery.BackColor = Color.LightGreen;
                txtQuery.BackColor = Color.LightGreen;
            }
            else TextChangedFromUserInput = true;
        }

        private void txtDPQuery_TextChanged(object sender, EventArgs e)
        {
            txtDPQuery.BackColor = Color.Empty;
            Parameterize();
            if (TextChangedFromUserInput)
            {
                TextChangedFromUserInput = false;
                txtQuery.BackColor = Color.LightGreen;
            }
            else TextChangedFromUserInput = true;
            
        }

        private void frmMain_Resize(object sender, EventArgs e)
        {
            dgQueries.Columns["QueryColumn"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            dgQueries.Columns["QueryColumn"].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            this.linkLabel1.LinkVisited = true;
            if (IsKudosSiteReachable)
                System.Diagnostics.Process.Start("http://kudos/SendKudos.aspx?alias=jburchel");
            else
                System.Diagnostics.Process.Start("mailto:todd.dedecker@microsoft.com&subject=Feedback on the SSAS Long Running Query Performance Helper&cc=jburchel@microsoft.com");
        }

        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            this.linkLabel2.LinkVisited = true;
            System.Diagnostics.Process.Start("mailto:jburchel@microsoft.com&subject=Feedback on the SSAS Long Running Query Performance Helper");
        }

        private void linkLabel3_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            this.linkLabel1.LinkVisited = true;
            System.Diagnostics.Process.Start("https://asqueryperfhelper.codeplex.com/");
        }

        private void txtQueryParts_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Control && (e.KeyValue == 'A' || e.KeyValue == 'a'))
                ((RichTextBox)sender).SelectAll();
            
        }

        void Form_DragEnter(object sender, DragEventArgs e)
        {
            if (e.Data.GetDataPresent(DataFormats.FileDrop))
                e.Effect = DragDropEffects.Copy; 
            else
                e.Effect = DragDropEffects.None; 

        }

        void Form_DragDrop(object sender, DragEventArgs e)
        {
            string[] FileList = (string[])e.Data.GetData(DataFormats.FileDrop, false);

            foreach (string File in FileList)
            {
                openFileDialog1.FileName = File;
                ProcessTraceWithUI();
                break;
            }
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            // Set file extension to the way we want to handle it, not ClickOnce...  ugly hack but works.
            RegistryKey r;
            if (Registry.CurrentUser.OpenSubKey("Software\\Classes\\SSASQryPrfHlpr.Program") == null)
                r = Registry.CurrentUser.CreateSubKey("Software\\Classes\\SSASQryPrfHlpr.Program").CreateSubKey("shell").CreateSubKey("open").CreateSubKey("command");
            else
                r = Registry.CurrentUser.CreateSubKey("Software\\Classes\\SSASQryPrfHlpr.Program\\shell\\open\\command", RegistryKeyPermissionCheck.ReadWriteSubTree);
            r.SetValue("", "\"" + Application.ExecutablePath + "\" \"%1\"");
            if (Registry.CurrentUser.OpenSubKey("Software\\Classes\\.trc") == null)
                r = Registry.CurrentUser.CreateSubKey("Software\\Classes\\.trc");
            else
                r = Registry.CurrentUser.OpenSubKey("Software\\Classes\\.trc");
            if (r.OpenSubKey("OpenWithProgids") == null)
                r = r.CreateSubKey("OpenWithProgids");
            else
                r = r.OpenSubKey("OpenWithProgids", true);
            r.SetValue("SSASQryPrfHlpr.Program", "");

            // Restore persisted settings
            chkRollover.Checked = Properties.Settings.Default.Rollover;
            chkDistinct.Checked = Properties.Settings.Default.Distinct;
            chkIncludeNoDurationQueries.Checked = Properties.Settings.Default.Incomplete;
            udLongestCount.Value = Properties.Settings.Default.TopN;


            // Process command line
            if (Environment.GetCommandLineArgs().Length > 1)
            {
                openFileDialog1.FileName = Environment.GetCommandLineArgs()[1];
                ProcessTraceWithUI();
            }          
        }

        private void frmMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            // Save settings
            Properties.Settings.Default.Rollover = chkRollover.Checked;
            Properties.Settings.Default.Distinct = chkDistinct.Checked;
            Properties.Settings.Default.Incomplete = chkIncludeNoDurationQueries.Checked;
            Properties.Settings.Default.TopN = (int)udLongestCount.Value;
            Properties.Settings.Default.Save();
        }

        private void UpdateCallback()
        {
            this.Invoke(new UpdateGridDelegate(UpdateGrid));
        }

        private void SetTextCallback(string text)
        {
            this.Invoke(new Action(() => { this.Text = text; }));
        }

    }
}
